package com.teamsun.util;

import com.teamsun.entity.TableHeader;
import com.teamsun.exception.ServiceException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>读取Excel表头</p>
 */
public class ReadExcelHeader {

    /**
     * 解析Excel表头(Excel格式为xlsx)
     * @param inputStream Excel文件输入流
     * @return Excel表头
     */
    public List<List<TableHeader>> parseXlsxExcelHeader(InputStream inputStream) {
        List<List<TableHeader>> list = new ArrayList<List<TableHeader>>();
        try {
            // 解析Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            list = readExcelHeader(sheet);
        } catch (Exception e) {
            throw new ServiceException("解析Excel表头异常:" + e.getMessage());
        }
        return list;
    }

    /**
     * 解析Excel表头(Excel格式为xls)
     * @param inputStream Excel文件输入流
     * @return Excel表头信息
     */
    public List<List<TableHeader>> parseXlsExcelHeader(InputStream inputStream) {
        List<List<TableHeader>> list = new ArrayList<List<TableHeader>>();
        try {
            // 解析Excel文件
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            HSSFSheet sheet = workbook.getSheetAt(0);
            list = readExcelHeader(sheet);
        } catch (Exception e) {
            throw new ServiceException("解析Excel表头异常:" + e.getMessage());
        }
        return list;
    }

    /**
     * 读取Excel表头
     * @param sheet Excel文件中指定的sheet
     * @return
     */
    private List<List<TableHeader>> readExcelHeader(Sheet sheet) {
        // 保存Excel表头信息，对于复杂表头，表头可能包含多行,所以需要嵌套List
        List<List<TableHeader>> list = new ArrayList<List<TableHeader>>();

        // 保存复杂表头中行合并单元格的列名信息
        List<String> rowMergeInfo = new ArrayList<String>();

        // 读取表头
        for(int i = 0; i <= sheet.getLastRowNum(); i ++) {
            Row row = sheet.getRow(i);

            // 保存每行的表头信息(复杂表头有多行数据)
            List<TableHeader> headers = new ArrayList<TableHeader>();

            // 保存列合并单元格列名信息
            List<String> colMergeInfo = new ArrayList<String>();

            // 统计列合并单元格中合并列的列数(key-列名  value-合并的列数)
            Map<String, Object> colMergeCnt = new HashMap<String, Object>();

            // 循环读取每个单元格的信息
            for(Cell cell : row) {
                // 判断单元格是否是合并单元格
                Map<String, Object> mergeResult = isMergedRegion(sheet, i, cell.getColumnIndex());
                Boolean isMerge = (Boolean) mergeResult.get("flag");
                if(isMerge) {  // 如果单元格是合并单元格
                    // 获取合并单元格的值
                    String colValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
                    colValue = colValue.replaceAll("(\\r\\n|\\n|\\n\\r)","<br>");
                    Integer colSpan = (Integer) mergeResult.get("colSpan");  // 合并列数
                    Integer rowSpan = (Integer) mergeResult.get("rowSpan");  // 合并行数

                    // 如果是合并列单元格
                    if(!colMergeInfo.contains(colValue) && colSpan != 0) {
                        // 初始化表头信息
                        TableHeader tableHeader = new TableHeader();
                        tableHeader.setColspan(colSpan + 1);
                        tableHeader.setField("");
                        tableHeader.setTitle(colValue);
                        colMergeInfo.add(colValue);

                        // 保存表头信息
                        headers.add(tableHeader);
                        colMergeCnt.put(colValue, 0);
                    } else if(colMergeInfo.contains(colValue) && colSpan != 0) {
                        // 为防止表头中存在重复的列名，每次读列合并单元格后计数器加1
                        // 当计数器和合并的列数相等时表示该列合并单元格读取完毕
                        // 同时将列名信息从colMergeInfo中删除，那么如果存在相同的列名则不会跳过
                        Integer mergeCnt = (Integer) colMergeCnt.get(colValue);
                        mergeCnt = mergeCnt + 1;
                        colMergeCnt.put(colValue, mergeCnt);
                        if(colSpan == mergeCnt) {
                            colMergeInfo.remove(colValue);
                            colMergeCnt.remove(colValue);
                        }
                    }

                    // 如果是行合并单元格
                    if(!rowMergeInfo.contains(colValue+cell.getColumnIndex()) && rowSpan != 0) {
                        // 初始化表头信息
                        TableHeader tableHeader = new TableHeader();
                        tableHeader.setRowspan(rowSpan + 1);
                        tableHeader.setField("test");
                        tableHeader.setTitle(colValue);
                        // 保存表头
                        headers.add(tableHeader);

                        // 为防止行合并单元格存在重复的列名，保存列名时在列名后加列索引
                        rowMergeInfo.add(colValue + cell.getColumnIndex());
                    }
                } else {  // 如果是普通的单元格，不是合并单元格
                    // 获取单元格信息
                    String colValue = cell.getRichStringCellValue().getString();
                    colValue = colValue.replaceAll("(\\r\\n|\\n|\\n\\r)","<br>");

                    // 初始化表头信息
                    TableHeader tableHeader = new TableHeader();
                    tableHeader.setTitle(colValue);

                    // 保存表头信息
                    headers.add(tableHeader);
                }
            }
            list.add(headers);
        }

        return list;
    }

    /**
     * 判断单元格是否是合并单元格
     * @param sheet Excel中的sheet
     * @param row sheet中对应的行索引
     * @param column 行中的列索引
     * @return
     */
    private Map<String, Object> isMergedRegion(Sheet sheet,int row ,int column) {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    resultMap.put("flag", true);  // 单元格是合并单元格
                    resultMap.put("colSpan", lastColumn - firstColumn);  // 单元格合并的列数
                    resultMap.put("rowSpan", lastRow - firstRow);  // 单元格合并的行数
                    return resultMap;
                }
            }
        }
        resultMap.put("flag", false);  // 单元格不是合并单元格
        return resultMap;
    }

    /**
     * 获取合并单元格的中的值
     * @param sheet Excel中的sheet
     * @param row sheet中对应的行索引
     * @param column 行中的列索引
     * @return
     */
    public String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){

                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }

    /**
     * 获取单元格的值
     * @param cell 单元格
     * @return
     */
    public String getCellValue(Cell cell){

        if(cell == null) {
            return "";
        }

        if(cell.getCellType() == Cell.CELL_TYPE_STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

            return String.valueOf(cell.getNumericCellValue());

        }
        return "";
    }

    /**
     * 获取报表表头信息(xls文件)
     * @param inputStream
     * @param headIndex
     * @return
     */
    public List<String> getXlsExcelHeaderInfo(InputStream inputStream, int headIndex) {
        List<String> titleList = new ArrayList<String>();
        try {
            // 解析Excel文件
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            HSSFSheet sheet = workbook.getSheetAt(0);
            int titleIndex = headIndex - 1;
            Row row = sheet.getRow(titleIndex);
            for(int i=0; i<row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                String title = cell.getStringCellValue();
                if(StringUtils.isBlank(title)) {
                    for(int j=titleIndex - 1; j>=0; j--) {
                        Row titleRow = sheet.getRow(j);
                        Cell titleCell = titleRow.getCell(i);
                        String realTitle = titleCell.getStringCellValue();
                        if(StringUtils.isNotBlank(realTitle)) {
                            title = realTitle;
                            break;
                        }
                    }
                }
                titleList.add(title);
            }
        } catch (Exception e) {
            throw new ServiceException("解析Excel表头异常:" + e.getMessage());
        }
        return titleList;
    }

    /**
     * 获取报表表头信息(xlsx文件)
     * @param inputStream
     * @param headIndex
     * @return
     */
    public List<String> getXlsxExcelHeaderInfo(InputStream inputStream, int headIndex) {
        List<String> titleList = new ArrayList<String>();
        try {
            // 解析Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            int titleIndex = headIndex - 1;
            Row row = sheet.getRow(titleIndex);
            for(int i=0; i<row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                String title = cell.getStringCellValue();
                title = title.replaceAll("(\\r\\n|\\n|\\n\\r)","<br>");
                if(StringUtils.isBlank(title)) {
                    for(int j=titleIndex - 1; j>=0; j--) {
                        Row titleRow = sheet.getRow(j);
                        Cell titleCell = titleRow.getCell(i);
                        String realTitle = titleCell.getStringCellValue();
                        if(StringUtils.isNotBlank(realTitle)) {
                            title = realTitle;
                            title = title.replaceAll("(\\r\\n|\\n|\\n\\r)","<br>");
                            break;
                        }
                    }
                }
                titleList.add(title);
            }
        } catch (Exception e) {
            throw new ServiceException("解析Excel表头异常:" + e.getMessage());
        }
        return titleList;
    }
}
